package com.anolesoft.twelvePlan.mapper;

import cn.hutool.core.util.StrUtil;
import com.anolesoft.twelvePlan.dto.PlanFenXiDTO;
import com.anolesoft.twelvePlan.dto.PlanFenXiJxDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

/**
 * @author zyq
 * @date 2022/7/18
 * 厂级月份计划预分析
 */
@Mapper
public interface PlanFenXiMapper {
    @SelectProvider(type = createSQL.class, method = "queryFenXiSQL")
    List<PlanFenXiDTO> queryFenXi(@Param("planFenXiDTO") PlanFenXiDTO planFenXiDTO);
    public static class createSQL {
        public static String queryFenXiSQL(@Param("planFenXiDTO") PlanFenXiDTO planFenXiDTO) {
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    //总数
                    sql.append(" '1' || rownum as row_num, a.innerwork,\n" +
                            "(a.date1+a.date2+a.date3+a.date4+a.date5+a.date6+a.date7) as total,\n" +
                            "a.date1,a.date2,a.date3,a.date4,a.date5,a.date6,a.date7 from\n" +
                            "(select '0' as innerwork,\n" +
                            "nvl(a.date1,0) as date1,nvl(b.date2,0) as date2,nvl(c.date3,0) as date3,nvl(d.date4,0) as date4,\n" +
                            "nvl(e.date5,0) as date5,nvl(f.date6,0) as date6,nvl(g.date7,0) as date7 from");
                    sql.append("(select a.checktime2, count(*) as date1 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and ((a.waixie != '外协' and a.innerwork is not null) or (a.waixie = '外协' and a.waixiefactory is not null))\n" +
                            "and a.plantime is not null");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) h left join");
                    sql.append("(select a.checktime2, count(*) as date1 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and ((a.waixie != '外协' and a.innerwork is not null) or (a.waixie = '外协' and a.waixiefactory is not null))\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-01', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-06', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) a on h.checktime2 = a.checktime2 left join");
                    sql.append("(select a.checktime2, count(*) as date2 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and ((a.waixie != '外协' and a.innerwork is not null) or (a.waixie = '外协' and a.waixiefactory is not null))\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-06', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-11', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) b on h.checktime2 = b.checktime2 left join");
                    sql.append("(select a.checktime2, count(*) as date3 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and ((a.waixie != '外协' and a.innerwork is not null) or (a.waixie = '外协' and a.waixiefactory is not null))\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-11', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-16', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) c on h.checktime2 = c.checktime2 left join");
                    sql.append("(select a.checktime2, count(*) as date4 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and ((a.waixie != '外协' and a.innerwork is not null) or (a.waixie = '外协' and a.waixiefactory is not null))\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-16', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-21', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) d on h.checktime2 = d.checktime2 left join");
                    sql.append("(select a.checktime2, count(*) as date5 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and ((a.waixie != '外协' and a.innerwork is not null) or (a.waixie = '外协' and a.waixiefactory is not null))\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-21', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-26', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) e on h.checktime2 = e.checktime2 left join");
                    sql.append("(select a.checktime2, count(*) as date6 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and ((a.waixie != '外协' and a.innerwork is not null) or (a.waixie = '外协' and a.waixiefactory is not null))\n" +
                            "and a.plantime > to_date(#{planFenXiDTO.checktime2}||'-26', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) f on h.checktime2 = f.checktime2 left join");
                    sql.append("(select a.checktime2, count(*) as date7 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and ((a.waixie != '外协' and a.innerwork is not null) or (a.waixie = '外协' and a.waixiefactory is not null))\n" +
                            "and a.plantime = to_date(#{planFenXiDTO.checktime2}||'-26', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) g on h.checktime2 = g.checktime2) a");

                    sql.append(" union ");

                    //工段
                    sql.append("select '2' || rownum as row_num, b.innerwork,\n" +
                            "(b.date1+b.date2+b.date3+b.date4+b.date5+b.date6+b.date7) as total,\n" +
                            "b.date1,b.date2,b.date3,b.date4,b.date5,b.date6,b.date7 from\n" +
                            "(select h.innerwork,\n" +
                            "nvl(a.date1,0) as date1,nvl(b.date2,0) as date2,nvl(c.date3,0) as date3,nvl(d.date4,0) as date4,\n" +
                            "nvl(e.date5,0) as date5,nvl(f.date6,0) as date6,nvl(g.date7,0) as date7 from");
                    sql.append("(select a.innerwork from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2} and a.plantime is not null\n" +
                            "and a.waixie != '外协' and a.innerwork is not null\n" +
                            "group by a.innerwork) h left join");
                    sql.append(" (select a.innerwork, count(*) as date1 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie != '外协' and a.innerwork is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-01', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-06', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.innerwork) a on h.innerwork = a.innerwork left join");
                    sql.append(" (select a.innerwork, count(*) as date2 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie != '外协' and a.innerwork is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-06', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-11', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.innerwork) b on h.innerwork = b.innerwork left join");
                    sql.append(" (select a.innerwork, count(*) as date3 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie != '外协' and a.innerwork is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-11', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-16', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.innerwork) c on h.innerwork = c.innerwork left join");
                    sql.append(" (select a.innerwork, count(*) as date4 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie != '外协' and a.innerwork is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-16', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-21', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.innerwork) d on h.innerwork = d.innerwork left join");
                    sql.append(" (select a.innerwork, count(*) as date5 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie != '外协' and a.innerwork is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-21', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-26', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.innerwork) e on h.innerwork = e.innerwork left join");
                    sql.append(" (select a.innerwork, count(*) as date6 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.innerwork is not null\n" +
                            "and a.plantime > to_date(#{planFenXiDTO.checktime2}||'-26', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.innerwork) f on h.innerwork = f.innerwork left join");
                    sql.append(" (select a.innerwork, count(*) as date7 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie != '外协' and a.innerwork is not null\n" +
                            "and a.plantime = to_date(#{planFenXiDTO.checktime2}||'-26', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.innerwork) g on h.innerwork = g.innerwork");
                    sql.append(" order by h.innerwork) b");

                    sql.append(" union ");

                    //外协总数
                    sql.append(" select '3' || rownum as row_num, c.innerwork,\n" +
                            "(c.date1+c.date2+c.date3+c.date4+c.date5+c.date6+c.date7) as total,\n" +
                            "c.date1,c.date2,c.date3,c.date4,c.date5,c.date6,c.date7 from\n" +
                            "(select '50' as innerwork,\n" +
                            "nvl(a.date1,0) as date1,nvl(b.date2,0) as date2,nvl(c.date3,0) as date3,nvl(d.date4,0) as date4,\n" +
                            "nvl(e.date5,0) as date5,nvl(f.date6,0) as date6,nvl(g.date7,0) as date7 from");
                    sql.append(" (select a.checktime2, count(*) as date1 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2} and a.plantime is not null\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) h left join");
                    sql.append(" (select a.checktime2, count(*) as date1 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-01', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-06', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) a on h.checktime2 = a.checktime2 left join");
                    sql.append(" (select a.checktime2, count(*) as date2 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-06', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-11', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) b on h.checktime2 = b.checktime2 left join");
                    sql.append(" (select a.checktime2, count(*) as date3 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-11', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-16', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) c on h.checktime2 = c.checktime2 left join");
                    sql.append(" (select a.checktime2, count(*) as date4 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-16', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-21', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) d on h.checktime2 = d.checktime2 left join");
                    sql.append(" (select a.checktime2, count(*) as date5 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-21', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-26', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) e on h.checktime2 = e.checktime2 left join");
                    sql.append(" (select a.checktime2, count(*) as date6 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime > to_date(#{planFenXiDTO.checktime2}||'-26', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) f on h.checktime2 = f.checktime2 left join");
                    sql.append(" (select a.checktime2, count(*) as date7 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime = to_date(#{planFenXiDTO.checktime2}||'-26', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.checktime2) g on h.checktime2 = g.checktime2) c");
                    
                    sql.append(" union ");

                    //外协
                    sql.append(" select '4' || rownum as row_num, d.innerwork,\n" +
                            "(d.date1+d.date2+d.date3+d.date4+d.date5+d.date6+d.date7) as total,\n" +
                            "d.date1,d.date2,d.date3,d.date4,d.date5,d.date6,d.date7 from\n" +
                            "(select h.waixiefactory as innerwork,\n" +
                            "nvl(a.date1,0) as date1,nvl(b.date2,0) as date2,nvl(c.date3,0) as date3,nvl(d.date4,0) as date4,\n" +
                            "nvl(e.date5,0) as date5,nvl(f.date6,0) as date6,nvl(g.date7,0) as date7 from");
                    sql.append(" (select a.waixiefactory from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2} and a.plantime is not null\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.waixiefactory) h left join");
                    sql.append(" (select a.waixiefactory, count(*) as date1 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-01', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-06', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.waixiefactory) a on h.waixiefactory = a.waixiefactory left join");
                    sql.append(" (select a.waixiefactory, count(*) as date2 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-06', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-11', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.waixiefactory) b on h.waixiefactory = b.waixiefactory left join\n");
                    sql.append(" (select a.waixiefactory, count(*) as date3 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-11', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-16', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.waixiefactory) c on h.waixiefactory = c.waixiefactory left join\n");
                    sql.append(" (select a.waixiefactory, count(*) as date4 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-16', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-21', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.waixiefactory) d on h.waixiefactory = d.waixiefactory left join\n");
                    sql.append(" (select a.waixiefactory, count(*) as date5 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime >= to_date(#{planFenXiDTO.checktime2}||'-21', 'yyyy-mm-dd')\n" +
                            "and a.plantime < to_date(#{planFenXiDTO.checktime2}||'-26', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.waixiefactory) e on h.waixiefactory = e.waixiefactory left join\n");
                    sql.append(" (select a.waixiefactory, count(*) as date6 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime > to_date(#{planFenXiDTO.checktime2}||'-26', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.waixiefactory) f on h.waixiefactory = f.waixiefactory left join\n");
                    sql.append(" (select a.waixiefactory, count(*) as date7 from mes_plan_xiada a\n" +
                            "where a.factory_id = #{planFenXiDTO.factoryId} and a.checktime2 = #{planFenXiDTO.checktime2}\n" +
                            "and a.waixie = '外协' and a.waixiefactory is not null\n" +
                            "and a.plantime = to_date(#{planFenXiDTO.checktime2}||'-26', 'yyyy-mm-dd')");
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getJx())) {
                        sql.append(" and a.jx in (");
                        sql.append(planFenXiDTO.getJx());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planFenXiDTO.getPlangrade())) {
                        sql.append(" and a.plangrade in (");
                        sql.append(planFenXiDTO.getPlangrade());
                        sql.append(")");
                    }
                    sql.append(" group by a.waixiefactory) g on h.waixiefactory = g.waixiefactory\n");
                    sql.append(" order by h.waixiefactory) d");
                    System.out.println(">>queryFenXiSQL:" + sql.toString());
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

    /**
     * 查询页面机型下拉选
     * 数据源：mes_plan_xiada_jixing每月已下达机型表
     * @param planFenXiJxDTO
     * @return
     */
    @Select("select a.jixing, a.objuid from mes_plan_xiada_jixing a " +
            "where a.factory_id = #{planFenXiJxDTO.factoryId}" +
            "and a.nianyue = #{planFenXiJxDTO.nianyue}")
    List<PlanFenXiJxDTO> selectJx(@Param("planFenXiJxDTO") PlanFenXiJxDTO planFenXiJxDTO);
}
